﻿Imports System.Data.OracleClient
Imports Microsoft.VisualBasic.Devices
Imports System.Xml
Imports WallpaperManager.Utils

Public Class OracleWallpaperService
  Inherits WallpaperService

  Public Sub New(ByVal connection As OracleConnection)
    MyBase.New(connection)
  End Sub

  Public Overrides Sub IncrementWallpaperSet(ByRef value As Wallpaper)
    Dim cmd As OracleCommand = _connection.CreateCommand()
    Dim sqls As String

    sqls = "UPDATE skins SET s_set = nvl(s_set,0) + 1 WHERE s_filename = :filename"

    cmd.CommandText = sqls
    cmd.Parameters.Add(New OracleParameter("filename", OracleType.VarChar)).Value = value.Filename
    cmd.ExecuteNonQuery()

    cmd.Dispose()

    value.Gesetzt += 1
  End Sub

  Public Overrides ReadOnly Property CheckOrgaIDexists(ByVal orga_id As Integer, ByVal resolution As String) As Boolean
    Get
      Dim cmd As OracleCommand = _connection.CreateCommand()
      Dim da As OracleDataAdapter
      Dim ds As New DataSet
      Dim sqls As String

      sqls = "SELECT decode(count(*), 0, 'false', 'true') exist "
      sqls &= vbNewLine & " FROM skins "
      sqls &= vbNewLine & " WHERE s_organr = :organr "
      sqls &= vbNewLine & "   AND s_resolution = :resolution "

      cmd.CommandText = sqls

      cmd.Parameters.Add(New OracleParameter("organr", OracleType.Number)).Value = orga_id
      cmd.Parameters.Add(New OracleParameter("resolution", OracleType.VarChar)).Value = resolution

      da = New OracleDataAdapter(cmd)
      da.Fill(ds)

      If ds.Tables(0).Rows.Count > 0 Then
        Return Boolean.Parse(ds.Tables(0).Rows(0).Item("exist"))
      Else
        cmd.Dispose()
        Return False
      End If

    End Get
  End Property

  Public Overrides ReadOnly Property GetWallpaperById(ByVal wallpaper_id As Integer) As Wallpaper
    Get
      Dim cmd As OracleCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim result As ArrayList
      Dim w As New Wallpaper(Me)

      sqls = "SELECT * FROM skins WHERE s_id = :wall_id"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New OracleParameter("wall_id", OracleType.Number)).Value = wallpaper_id

      result = Utils.GetArrayListFromReader(cmd.ExecuteReader(), Utils.eColumnCaseDefinition.ToLower)

      For Each row As Hashtable In result
        w.Datum = row("s_date")
        w.Favorite = row("s_favorite")
        w.Filename = row("s_filename")
        w.FullName = row("s_true_name")
        w.Gesetzt = row("s_set")
        w.ID = row("s_id")
        w.Name = row("s_name")
        w.OrgaID = row("s_organr")
        w.Resolution = row("s_resolution")
      Next

      Return w

    End Get
  End Property



  Public Overrides ReadOnly Property IsFavoritePic(ByVal w As Wallpaper) As Boolean
    Get
      Dim cmd As OracleCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim da As OracleDataAdapter
      Dim ds As New DataSet

      sqls = "SELECT decode(s_favorite, 'Y', 'true', 'false') isfavpic FROM skins WHERE s_id = :id"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New OracleParameter("id", OracleType.Number)).Value = w.ID

      da = New OracleDataAdapter(cmd)
      da.Fill(ds)

      Return Boolean.Parse(ds.Tables(0).Rows(0).Item("isfavpic"))
    End Get
  End Property

  Public Overrides ReadOnly Property IsFavoriteModel(ByVal w As Wallpaper) As Boolean
    Get
      Dim cmd As OracleCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim da As OracleDataAdapter
      Dim ds As New DataSet

      sqls = "SELECT decode(count(*), 0, 'false', 'true') isfavmod FROM skins_favorites WHERE s_name = :sname"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New OracleParameter("sname", OracleType.VarChar)).Value = w.Name

      da = New OracleDataAdapter(cmd)
      da.Fill(ds)

      Return Boolean.Parse(ds.Tables(0).Rows(0).Item("isfavmod"))
    End Get
  End Property

  Public Overrides Sub SetFavoriteModel(ByRef w As Wallpaper)
    Dim cmd As OracleCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "insert into skins_favorites(s_name) values(:sname)"
    cmd.CommandText = sqls

    cmd.Parameters.Add(New OracleParameter("sname", OracleType.VarChar)).Value = w.Name

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides Sub SetFavoritePic(ByRef w As Wallpaper)
    Dim cmd As OracleCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "update skins set s_favorite = 'Y' where s_id = :id"
    w.Favorite = True
    cmd.CommandText = sqls

    cmd.Parameters.Add(New OracleParameter("id", OracleType.Number)).Value = w.ID

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides Sub RemoveFavoriteModel(ByRef w As Wallpaper)
    Dim cmd As OracleCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "delete from skins_favorites where s_name = :sname"
    cmd.CommandText = sqls

    cmd.Parameters.Add(New OracleParameter("sname", OracleType.VarChar)).Value = w.Name

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides Sub RemoveFavoritePic(ByRef w As Wallpaper)
    Dim cmd As OracleCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "update skins set s_favorite = null where s_id = :id"
    w.Favorite = False
    cmd.CommandText = sqls

    cmd.Parameters.Add(New OracleParameter("id", OracleType.Number)).Value = w.ID

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides ReadOnly Property GetNewDownloadableWallpaper() As DownloadabeWallpaper
    Get
      Dim cmd As OracleCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim da As OracleDataAdapter
      Dim ds As New DataSet
      Dim dw As New DownloadabeWallpaper(Me)

      sqls = "select skin_id.nextval newid from dual"

      cmd.CommandText = sqls

      da = New OracleDataAdapter(cmd)
      da.Fill(ds)

      dw.ID = ds.Tables(0).Rows(0).Item("newid")

      cmd.Dispose()

      dw.IsNew = True

      Return dw
    End Get
  End Property

  Public Overrides Sub InsertNewWallpaper(ByRef value As Wallpaper)
    Dim cmd As OracleCommand = _connection.CreateCommand()
    Dim sqls As String

    sqls = "INSERT INTO skins(s_filename, s_date, s_organr, s_name, s_resolution, s_id )"
    sqls &= vbNewLine & " VALUES( :filename, sysdate, :organr, :sname, :resolution, :id ) "

    cmd.CommandText = sqls

    cmd.Parameters.Add(New OracleParameter("filename", OracleType.VarChar)).Value = value.Filename
    cmd.Parameters.Add(New OracleParameter("organr", OracleType.Number)).Value = value.OrgaID
    cmd.Parameters.Add(New OracleParameter("sname", OracleType.VarChar)).Value = value.Name
    cmd.Parameters.Add(New OracleParameter("resolution", OracleType.VarChar)).Value = value.Resolution
    cmd.Parameters.Add(New OracleParameter("id", OracleType.Number)).Value = value.ID

    cmd.ExecuteNonQuery()

    cmd.Dispose()

    value.IsNew = False

    value = GetWallpaperById(value.ID)

  End Sub

  Public Overrides ReadOnly Property GetMainList(Optional ByVal filter As String = "", Optional ByVal filtername As String = "Filter") As ListInfoWallpaper()
    Get
      Dim cmd As OracleCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim liw() As ListInfoWallpaper
      Dim result As ArrayList

      sqls = " select * from ( "

      If filter <> "" Then
        sqls &= vbNewLine & "select -1 id, :user_filtername key, s_id, s_date, s_true_name, s_filename, s_resolution, s_true_name || ' ('|| s_filename || ')' display, 'false' namesort  "
        sqls &= vbNewLine & " from skins "
        sqls &= vbNewLine & " where s_id is not null "
        sqls &= vbNewLine & " and s_filename is not null"
        sqls &= vbNewLine & " and (s_filename like '%'||lower(:user_filter)||'%' "
        sqls &= vbNewLine & " or lower(s_true_name) like '%'||lower(:user_filter)||'%' ) "
        sqls &= vbNewLine & " union"
        cmd.Parameters.Add(New OracleParameter("user_filter", OracleType.VarChar)).Value = filter
        cmd.Parameters.Add(New OracleParameter("user_filtername", OracleType.VarChar)).Value = filtername
      End If
      sqls &= vbNewLine & _config.ÜbersichtSQLOracle
      sqls &= vbNewLine & " ) t "
      sqls &= vbNewLine & " where s_resolution like :resolution "
      sqls &= vbNewLine & " order by id, s_true_name, s_date"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New OracleParameter("resolution", OracleType.VarChar)).Value = _config.Resolution

      result = Utils.GetArrayListFromReader(cmd.ExecuteReader(), Utils.eColumnCaseDefinition.ToLower)

      If result.Count > 0 Then
        ReDim liw(result.Count - 1)
        Dim i As Integer = 0
        For Each row As Hashtable In result
          liw(i) = New ListInfoWallpaper
          With liw(i)
            .SortName = row("key")
            .SortNumber = row("id")
            .ID = row("s_id")
            .Datum = row("s_date")
            .FullName = row("s_true_name")
            .FileName = row("s_filename")
            .Display = row("display")
            .NameSort = Boolean.Parse(row("namesort"))
          End With
          i += 1
        Next
      Else
        ReDim liw(-1)
      End If
      Return liw
    End Get
  End Property

  Public Overrides Sub DeleteWallpaper(ByVal w As Wallpaper, ByVal filesystem As Boolean)
    Dim cmd As OracleCommand = _connection.CreateCommand()
    Dim sqls As String

    sqls = "DELETE FROM skins WHERE s_filename = :sfilename"

    cmd.CommandText = sqls

    cmd.Parameters.Add(New OracleParameter("sfilename", OracleType.VarChar)).Value = w.Filename

    cmd.ExecuteNonQuery()

    MyBase.DeleteFromFilesystem(_config.SkinsPath & w.Filename)

  End Sub

  Public Overrides Sub UpdateWallpaper(ByVal w As Wallpaper)
    Dim cmd As OracleCommand = _connection.CreateCommand()
    Dim sqls As String

    sqls = "UPATE skins SET s_filename = :sfilename, s_date = :sdate, s_organr = :sorganr, s_name = :sname, s_resolution = :sresolution, "
    sqls &= "s_true_name = :struename, s_set = :sset, s_favorite = :sfav WHERE s_id = :sid "

    cmd.CommandText = sqls

    cmd.Parameters.Add(New OracleParameter("sfilename", OracleType.VarChar)).Value = w.Filename
    cmd.Parameters.Add(New OracleParameter("sdate", OracleType.DateTime)).Value = w.Datum
    cmd.Parameters.Add(New OracleParameter("sorganr", OracleType.Number)).Value = w.OrgaID
    cmd.Parameters.Add(New OracleParameter("sname", OracleType.VarChar)).Value = w.Name
    cmd.Parameters.Add(New OracleParameter("sresolution", OracleType.VarChar)).Value = w.Resolution
    cmd.Parameters.Add(New OracleParameter("struename", OracleType.VarChar)).Value = w.FullName
    cmd.Parameters.Add(New OracleParameter("sset", OracleType.Number)).Value = w.Gesetzt
    cmd.Parameters.Add(New OracleParameter("sfav", OracleType.VarChar)).Value = w.DbFavorite

    cmd.ExecuteNonQuery()

    cmd.Dispose()

  End Sub

  Public Overrides Function GetWallpaperByName(ByVal name As String) As List(Of Wallpaper)
    Dim cmd As OracleCommand = _connection.CreateCommand()
    'Dim w() As Wallpaper
    Dim low as New List(Of Wallpaper)
    Dim sqls As String
    Dim da As OracleDataAdapter
    Dim ds As New DataSet

    sqls = "select s_id from skins where s_name = :sname and s_resolution like :sresolution"

    cmd.CommandText = sqls

    cmd.Parameters.Add(New OracleParameter("sname", OracleType.VarChar)).Value = name
    cmd.Parameters.Add(New OracleParameter("sresolution", OracleType.VarChar)).Value = _config.Resolution

    da = New OracleDataAdapter(cmd)
    da.Fill(ds)

    'ReDim w(ds.Tables(0).Rows.Count - 1)

    For i As Integer = 0 To ds.Tables(0).Rows.Count - 1 Step 1
      low.Add(GetWallpaperById(ds.Tables(0).Rows(i).Item("s_id")))
    Next

    Return low
  End Function

  Public Overrides ReadOnly Property GetListOfWallpaper(ByVal ids As list(of Integer)) As System.Collections.Generic.List(Of Wallpaper)
    Get
      Dim w As New List(Of Wallpaper)
      For Each i In ids
        w.Add(GetWallpaperById(i))
      Next
      Return w
    End Get
  End Property
  
  Public Overrides ReadOnly Property GetModelDetails() As List(Of ModelDetail)
  	Get
  		Dim md As New List(Of ModelDetail)
  		Dim cmd As OracleCommand = _connection.CreateCommand()
  		Dim sqls As String
  		Dim da As OracleDataAdapter 
  		Dim dt As New DataTable 
  		
  		sqls = vbNewLine & "select s_name,  "
		sqls &= vbNewLine & "       s_true_name,  "
		sqls &= vbNewLine & "       max(s_filename) preview "
		sqls &= vbNewLine & "from skins  "
		sqls &= vbNewLine & "where s_resolution = :resolution "
		sqls &= vbNewLine & "group by s_name, s_true_name "
		sqls &= vbNewLine & "order by s_name "
		
		cmd.CommandText = sqls 
		cmd.Parameters.Add("resolution", OracleType.VarChar).Value = _config.Resolution 
		
  		da = New OracleDataAdapter(cmd)
  		da.Fill(dt)
  		
  		For i As Integer = 0 To dt.Rows.Count - 1 Step 1
  			Dim tmd As New ModelDetail
  			tmd.DisplayName = Dbnull2nothing(dt.Rows(i).Item("s_true_name"))
  			tmd.Name = dbnull2nothing(dt.Rows(i).Item("s_name"))
  			tmd.Preview = (_config.SkinsPath & "\" & dbnull2nothing(dt.Rows(i).Item("preview")))
  			md.Add(tmd)
  		Next
  		return md
  	End Get
  End Property

End Class
